package dao;

import domain.Department;
import domain.Teacher;
import exception.BysjException;
import service.DepartmentService;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class TeacherDao {
    private static TeacherDao teacherDao = new TeacherDao();
    private static Collection<Teacher> teachers;
    //将唯一构造器设置成私有，阻止在本类外成本类的对象
    private TeacherDao(){
    }
    public static TeacherDao getInstance(){
        return teacherDao;
    }
    public Collection<Teacher> findAll(Connection connection, List<Condition>  conditionList, Pagination pagination) throws SQLException, BysjException {
        //创建Teacher类型集合类对象，HashSet不保证结果中元素的顺序
        Collection<Teacher> desiredTeachers = new HashSet<Teacher>();
        //创建查询教师的主语句
        StringBuilder sql_select = new StringBuilder("select * from teacher");
        //如果有条件，则生成条件子句，并附加到主语句的后面
        if (conditionList != null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        //获取Teacher中的元组总数
        int totalNum = CommonDao.getInstance().count(connection,"Teacher");
        //获得查询教师的语句对象
        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt_select.executeQuery();
        //遍历resultSet
        while (resultSet.next()){
            //获得school表中当前元组的ID字段的值
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int departmentId = resultSet.getInt("department_id");
            Department department = DepartmentService.getInstance().find(departmentId);
            //以当前元组的各个字段值为参数，创建School对象
            Teacher teacher = new Teacher(id,name,department);
            //将当前元组代表的School对象添加到DesiredSchools中
            desiredTeachers.add(teacher);
        }
        JdbcHelper.close(resultSet,pstmt_select,connection);
        return desiredTeachers;
    }
    public boolean add(Connection connection,Teacher teacher) throws SQLException, BysjException {
        //获取数据库连接
        //准备预编译的语句
        String sql_insert = "insert into teacher(name,department_id,department) " +
                " values (?,?,?)";
        PreparedStatement pstmt_insert = connection.prepareStatement(sql_insert);
        pstmt_insert.setString(1,teacher.getName());
        pstmt_insert.setInt(2,teacher.getDepartment().getId());
        pstmt_insert.setString(3,teacher.getDepartment().getDescription());
        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_insert.executeUpdate();
        return rowAffected > 0;
    }
    public Teacher find(int id)throws SQLException {
        //创建School类型的引用，暂时不创建对象
        Teacher desiredTeacher = null;
        //获得数据库的连接
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "select * from teacher where id=?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        //如果表中存在id对应的元组，则获得表中的字段值，并创建对象
        if (resultSet_select_id.next()){
            String name = resultSet_select_id.getString("name");
            int department_id = resultSet_select_id.getInt("department_id");
            Department department = DepartmentService.getInstance().find(department_id);
            desiredTeacher = new Teacher(id,name,department);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet_select_id,pstmt_select_id,connection);
        return desiredTeacher;
    }
    public boolean delete(int id) throws SQLException{
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句中的空格
        PreparedStatement preparedStatement = connection.prepareStatement("delete from teacher where id=?");
        //对预编译语句对象的参数赋值
        preparedStatement.setInt(1,id);
        //执行预编译语句，用其返回值、影响的行数赋值给
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement,connection);
        return affectedRowNum>0;
    }
    public boolean update(Connection connection,Teacher teacher) throws SQLException, BysjException {
        //获取数据库连接
        //准备预编译的语句
        String sql_update = "update teacher " +
                "set name=? where id=2";
        PreparedStatement pstmt_insert = connection.prepareStatement(sql_update);
        pstmt_insert.setString(1,teacher.getName());
        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_insert.executeUpdate();
        return rowAffected > 0;
    }
}
